package com.jcx.ldzj.utile;

import com.jcx.ldzj.entity.GenTableColumnInfo;
import com.jcx.ldzj.entity.GenTableCretearInfo;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.format.*;
import jxl.write.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

/**
 * @author dyh
 * @create 2018-07-14 下午8:20
 * @desc excle表格功能编写
 **/
public class ExcelUtile {


    /**
     * 下载文件
     *
     * @return
     */
    public static void download(String filename, List<GenTableCretearInfo> genTableCretearInfos, HttpServletResponse response, HttpServletRequest request) {
        // 写到服务器上
        String path = request.getSession().getServletContext().getRealPath("") + "/" + filename;
        File name = new File(path);
        try {
            // 创建写工作簿对象
            WritableWorkbook workbook = Workbook.createWorkbook(name);
            addData(genTableCretearInfos, workbook);
            //开始执行写入操作
            workbook.write();
            //关闭流
            workbook.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        // 第六步，下载excel

        OutputStream out = null;
        try {

            // 1.弹出下载框，并处理中文
            //* 如果是从jsp页面传过来的话，就要进行中文处理，在这里action里面产生的直接可以用
//           String filename = request.getParameter("filename");
//             if (request.getMethod().equalsIgnoreCase("GET")) {
//             filename = new String(filename.getBytes("iso8859-1"), "utf-8");
//             }

            response.addHeader("content-disposition", "attachment;filename="
                    + java.net.URLEncoder.encode(filename, "utf-8"));

            // 2.下载
            out = response.getOutputStream();
            String path3 = request.getSession().getServletContext().getRealPath("") + "/" + filename;

            // inputStream：读文件，前提是这个文件必须存在，要不就会报错
            InputStream is = new FileInputStream(path3);

            byte[] b = new byte[4096];
            int size = is.read(b);
            while (size > 0) {
                out.write(b, 0, size);
                size = is.read(b);
            }
            out.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            name.delete();
        }
    }

    /**
     * 生产第一个sheet，用于表汇总
     * @param genTableCretearInfos 表的list
     * @param workbook
     * @throws WriteException
     */
    private static void addData(List<GenTableCretearInfo> genTableCretearInfos, WritableWorkbook workbook) throws WriteException {
        // 工作表
        WritableSheet sheet = workbook.createSheet("表清单", 0);
        // 设置字体;
        WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

        WritableCellFormat cellFormat = new WritableCellFormat(font);
        // 设置背景颜色;
        cellFormat.setBackground(Colour.WHITE);
        // 设置边框;
//            cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
        cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
        // 设置文字居中对齐方式;
        cellFormat.setAlignment(Alignment.CENTRE);
        sheet.setColumnView(0, 15);
        // 设置自动换行;
        cellFormat.setWrap(true);
        // 设置垂直居中;
        cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        // 单元格
        Label label0 = new Label(1, 0, "表描述", cellFormat);
        Label label1 = new Label(2, 0, "表名称", cellFormat);
        sheet.addCell(label0);
        sheet.addCell(label1);
        sheet.getSettings().setDefaultColumnWidth(50);
        // 给第二行设置背景、字体颜色、对齐方式等等;
        WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
        // 设置文字居中对齐方式;
        cellFormat2.setAlignment(Alignment.CENTRE);
        // 设置垂直居中;
        cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat2.setBackground(Colour.WHITE);
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
        cellFormat2.setWrap(true);
        for (int i = 0; i <genTableCretearInfos.size() ; i++) {
            GenTableCretearInfo genTableCretearInfo = genTableCretearInfos.get(i);
            Label lt1 = new Label(1, i+1,genTableCretearInfo.getTableComment() , cellFormat2);
            Label lt2 = new Label(2, i+1, genTableCretearInfo.getTableName(), cellFormat2);
            sheet.addCell(lt1);
            sheet.addCell(lt2);
            List<GenTableColumnInfo> columnList = genTableCretearInfo.getColumnList();
            addData(genTableCretearInfo.getTableName(),columnList,workbook);
        }


    }


    /**
     * 生成各个sheet，每个数据库表对应一个sheet
     * @param columnList
     * @param workbook
     * @throws WriteException
     */
    private static void addData(String sheetName,List<GenTableColumnInfo> columnList, WritableWorkbook workbook) throws WriteException {
        // 工作表
        WritableSheet sheet = workbook.createSheet(sheetName, 0);
        // 分别给1列设置不同的宽度;
        sheet.setColumnView(0, 15);
        // 设置字体;
        WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        //写入列，对字体样式进行设置
        WritableCellFormat cellFormat = new WritableCellFormat(font);
        // 设置背景颜色;
        cellFormat.setBackground(Colour.WHITE);
        // 设置边框;
//            cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
        // 设置文字居中对齐方式;
        cellFormat.setAlignment(Alignment.CENTRE);

        // 设置自动换行;
        cellFormat.setWrap(true);
        // 设置垂直居中;
        cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        // 单元格
        Label label0 = new Label(1, 0, "列描述", cellFormat);
        Label label1 = new Label(2, 0, "列名称", cellFormat);
        Label label2 = new Label(3, 0, "列类型", cellFormat);
        Label label3 = new Label(4, 0, "列备注", cellFormat);
        sheet.addCell(label0);
        sheet.addCell(label1);
        sheet.addCell(label2);
        sheet.addCell(label3);
        // 给sheet电子版中所有的列设置默认的列的宽度;
        sheet.getSettings().setDefaultColumnWidth(50);
            // 给sheet电子版中所有的行设置默认的高度，高度的单位是1/20个像素点,但设置这个貌似就不能自动换行了
            // sheet.getSettings().setDefaultRowHeight(30 * 20);
        // 给第二行设置背景、字体颜色、对齐方式等等;
        WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
        // 设置文字居中对齐方式;
        cellFormat2.setAlignment(Alignment.CENTRE);
        // 设置垂直居中;
        cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
        // 设置背景颜色;
        cellFormat2.setBackground(Colour.WHITE);
        // 设置边框;
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
        // 设置自动换行;
        cellFormat2.setWrap(true);
        for (int i = 0; i <columnList.size() ; i++) {
            GenTableColumnInfo genTableColumnInfo = columnList.get(i);
            Label lt1 = new Label(1, i+1,genTableColumnInfo.getColumnComment() , cellFormat2);
            Label lt2 = new Label(2, i+1, genTableColumnInfo.getColumnName(), cellFormat2);
            Label lt3 = new Label(3, i+1, genTableColumnInfo.getDataType(), cellFormat2);
            sheet.addCell(lt1);
            sheet.addCell(lt2);
            sheet.addCell(lt3);
        }


    }
}